using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class PhieuThuePhongDAO
    {
        private PhieuThuePhongDTO[] ConvertToPhieuThuePhongDTO(DataTable dt)
        {
            PhieuThuePhongDTO[] Arr = new PhieuThuePhongDTO[0];
            try
            {
                int n = dt.Rows.Count;
                Arr = new PhieuThuePhongDTO[n];
                for (int i = 0; i < n; i++)
                {
                    Arr[i] = new PhieuThuePhongDTO();
                    Arr[i].MaPT = Convert.ToInt32(dt.Rows[i]["MaPT"].ToString());
                    Arr[i].MaKH = Convert.ToInt32(dt.Rows[i]["MaKH"].ToString());
                    Arr[i].MaP = Convert.ToInt32(dt.Rows[i]["MaP"].ToString());
                    Arr[i].DonGia = Convert.ToDouble(dt.Rows[i]["DonGia"].ToString());
                    Arr[i].NgayLapPhieu = Convert.ToDateTime(dt.Rows[i]["NgayLapPhieu"].ToString());
                    Arr[i].NgayBatDau = Convert.ToDateTime(dt.Rows[i]["NgayBatDau"].ToString());
                    Arr[i].TienCoc = Convert.ToDouble(dt.Rows[i]["TienCoc"].ToString());
                    Arr[i].TinhTrang = Convert.ToInt32(dt.Rows[i]["TinhTrang"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return Arr;
        }

        public PhieuThuePhongDTO[] LayPhieuThuePhongTheoMaKhachHang(int MaKH)
        {
            PhieuThuePhongDTO[] Arr = new PhieuThuePhongDTO[100];
            try
            {
                DataTable dt = new DataTable();
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_LayDanhSachPhieuThueTheoKhachHang";

                cmd.Parameters.Add("@MaKH", SqlDbType.Int);
                cmd.Parameters["@MaKH"].Value = MaKH;

                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                Arr = ConvertToPhieuThuePhongDTO(dt);

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return Arr;
        }

        public int ThemPhieuThuePhong(int MaPTP, int MaKH, int MaP, Double DonGia, DateTime NgayLap, DateTime NgayThue, Double TienCoc)
        {
            MaPTP = 0;
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ThemPhieuThue";

                cmd.Parameters.Add("@Ma", SqlDbType.Int);
                cmd.Parameters.Add("@MaKH", SqlDbType.Int);
                cmd.Parameters.Add("@MaP", SqlDbType.Int);
                cmd.Parameters.Add("@DonGia", SqlDbType.Money);
                cmd.Parameters.Add("@NgayLapPhieu", SqlDbType.DateTime);
                cmd.Parameters.Add("@NgayBatDau", SqlDbType.DateTime);
                cmd.Parameters.Add("@TienCoc", SqlDbType.Money);
                

                cmd.Parameters["@Ma"].Direction = ParameterDirection.Output;
                cmd.Parameters["@MaKH"].Value = MaKH;
                cmd.Parameters["@MaP"].Value = MaP;
                cmd.Parameters["@DonGia"].Value = DonGia;
                cmd.Parameters["@NgayLapPhieu"].Value = NgayLap;
                cmd.Parameters["@NgayBatDau"].Value = NgayThue;
                cmd.Parameters["@TienCoc"].Value = TienCoc;
                

                cmd.ExecuteNonQuery();

                MaPTP = Convert.ToInt32(cmd.Parameters["@Ma"].Value);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return MaPTP;
        }

        public void CapNhatPhieuThuePhong(int MaPTP, int MaKH, int MaP, Double DonGia, DateTime NgayLap, DateTime NgayThue, Double TienCoc,int TinhTrang)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_CapNhatPhieuThue";

                cmd.Parameters.Add("@Ma", SqlDbType.Int);
                cmd.Parameters.Add("@MaKH", SqlDbType.Int);
                cmd.Parameters.Add("@MaP", SqlDbType.Int);
                cmd.Parameters.Add("@DonGia", SqlDbType.Money);
                cmd.Parameters.Add("@NgayLapPhieu", SqlDbType.DateTime);
                cmd.Parameters.Add("@NgayBatDau", SqlDbType.DateTime);
                cmd.Parameters.Add("@TienCoc", SqlDbType.Money);
                cmd.Parameters.Add("@TinhTrang", SqlDbType.Int);
                
                cmd.Parameters["@Ma"].Value = MaPTP;
                cmd.Parameters["@MaKH"].Value = MaKH;
                cmd.Parameters["@MaP"].Value = MaP;
                cmd.Parameters["@DonGia"].Value = DonGia;
                cmd.Parameters["@NgayLapPhieu"].Value = NgayLap;
                cmd.Parameters["@NgayBatDau"].Value = NgayThue;
                cmd.Parameters["@TienCoc"].Value = TienCoc;
                cmd.Parameters["@TinhTrang"].Value = TinhTrang;

                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public PhieuThuePhongDTO[] TimKiemPhieuThuePhong(int MaKH, int MaPhong, int MaTT, Double DonGia, Double TienCoc, DateTime NgayLapTu, DateTime NgayLapDen, DateTime NgayBatDauTu, DateTime NgayBatDauDen)
        {
            PhieuThuePhongDTO[] Arr = new PhieuThuePhongDTO[100];
            string strMaKH = " 1 = 1 ";
            string strMaP = " 1 = 1 ";
            string strMaTT = " 1 = 1 ";
            string strDonGia = " 1 = 1 ";
            string strTienCoc = " 1 = 1 ";
            string strNgayLap = " 1 = 1 ";
            string strNgayBD = " 1 = 1 ";

            try
            {
                if (MaKH != 0)
                    strMaKH = " MaKH = " + MaKH;
                if (MaPhong != 0)
                    strMaP = " MaP = " + MaPhong;
                if (MaTT != 0)
                    strMaTT = " TinhTrang = " + MaTT;
                if (DonGia != 0)
                    strDonGia = " DonGia = " + DonGia;
                if (TienCoc != 0)
                    strTienCoc = " TienCoc = " + TienCoc;
                strNgayLap = "NgayLapPhieu BETWEEN '" + NgayLapTu + "' AND '" + NgayLapDen + "'";
                strNgayBD = "NgayBatDau BETWEEN '" + NgayBatDauTu + "' AND '" + NgayBatDauDen + "'";

                string strSQL = "SELECT * FROM PHIEUTHUEPHONG WHERE ";
                strSQL += strMaKH + " AND ";
                strSQL += strMaP + " AND ";
                strSQL += strMaTT + " AND ";
                strSQL += strDonGia + " AND ";
                strSQL += strTienCoc + " AND ";
                strSQL += strNgayLap + " AND ";
                strSQL += strNgayBD;

                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                DataTable dt = new DataTable();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);

                Arr = ConvertToPhieuThuePhongDTO(dt);

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return Arr;
        }

        public PhieuThuePhongDTO[] LayDanhSachPhieuThue()
        {
            PhieuThuePhongDTO[] Arr = new PhieuThuePhongDTO[100];
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                DataTable dt = new DataTable();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_LayDanhSachPhieuThue";

                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);

                Arr = ConvertToPhieuThuePhongDTO(dt);

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return Arr;
        }
    }
}
